Automatic Column Handling
Automatic Column Handling allows users to update chosen tables in the semantic model each time the model is processed by automatically adding new columns to the semantic model added physically to the underlying tables. Similarly, columns that have been removed from the physical tables will also be removed from the semantic model. This is especially useful for Direct Query models that need to be kept in sync with changes in the underlying database tables.
Columns in Tables are refreshed subject to settings when the model was processed. This can either be manually or via a schedule.
Columns may either be added or removed.
- Open the model containing the tables that need to be refreshed.
Scheduling
For information on Model Scheduling, please see Models Schedules.
The Automatic Column Handling feature is added in the same tab as the Models Schedules.
In the Job Details section, select SyncModelColumns. This is used to define the synchronization mode. There are three options:
- None - No columns in the model are synced. This is the default value.
- Sync All - Sync all the tables in the model.
- Sync Selected Tables - Sync only the selected tables in the model.
If the user selects Sync Selected Tables, they must select the tables that need to be synced.
To Sync Selected Tables
- For each table that needs to be synced, click on the Table's heading area (clicking on a column name will open the column feature, which has different options). In the Properties section, select Sync table columns
- In the Schedule Settings section, select Sync table columns. This will refresh the columns in that table when Column Refresh is performed on model processing.
Sync Column Settings
This feature allows users to define how each data type in a model will be defined.
In this case, when refreshing the columns, the sync defines how the new columns will be displayed in the database.
In the example below, if the new column is an Integer value, the column will be not displayed. If the column contains a Real value, it will be displayed, and any aggregate value for the column will display the column's sum.
This feature allows users to maintain control and uniformity across the data model.